<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
	<META HTTP-EQUIV="CONTENT-TYPE" CONTENT="text/html; charset=utf-8">
	<TITLE>Querying objects (tut3.cpp)</TITLE>
	<META NAME="GENERATOR" CONTENT="LibreOffice 3.5  (Linux)">
	<META NAME="AUTHOR" CONTENT="Viacheslav Naydenov">
	<META NAME="CREATED" CONTENT="20130520;10024900">
	<META NAME="CHANGEDBY" CONTENT="Viacheslav Naydenov">
	<META NAME="CHANGED" CONTENT="20141022;1012200">
	<STYLE TYPE="text/css">
	<!--
		@page { margin: 0.79in }
		P { margin-top: 0.04in; margin-bottom: 0.04in }
		H2 { margin-bottom: 0.08in }
		H2.western { font-family: "Times New Roman", serif }
		H2.cjk { font-family: "Droid Sans Fallback" }
		H2.ctl { font-family: "Lohit Hindi" }
		PRE.western { font-family: "Courier New", monospace }
		PRE.cjk { font-family: "Droid Sans Fallback", monospace }
		PRE.ctl { font-family: "Lohit Hindi", monospace }
		TT.western { font-family: "Courier New", monospace }
		TT.cjk { font-family: "Droid Sans Fallback", monospace }
		TT.ctl { font-family: "Lohit Hindi", monospace }
	-->
	</STYLE>
</HEAD>
<BODY LANG="en-US" DIR="LTR">
<H2 CLASS="western">Querying objects (tut3.cpp)</H2>
<P>We'll use the data schema from <A HREF="Tutorial2.en.html">Tutorial2</A>
&quot;Mapping related tables: one-to-many&quot;. There are two
tables: Clients (<TT CLASS="western">client_tbl</TT>) and Orders
(<TT CLASS="western">order_tbl</TT>), one client is supposed to have
zero or more orders. Let's define the tables, their mapping to
classes and their relationship in an XML file:</P>
<PRE CLASS="western">&lt;schema&gt;
    &lt;table name=&quot;client_tbl&quot; sequence=&quot;client_seq&quot; class=&quot;Client&quot; xml-name=&quot;client&quot;&gt;
        &lt;column name=&quot;id&quot; type=&quot;longint&quot;&gt;
            &lt;primary-key /&gt;
        &lt;/column&gt;
        &lt;column name=&quot;dt&quot; type=&quot;datetime&quot; null=&quot;false&quot; default=&quot;sysdate&quot; /&gt;
        &lt;column name=&quot;name&quot; type=&quot;string&quot; size=&quot;100&quot; null=&quot;false&quot; /&gt;
        &lt;column name=&quot;email&quot; type=&quot;string&quot; size=&quot;100&quot; null=&quot;false&quot; /&gt;
        &lt;column name=&quot;phone&quot; type=&quot;string&quot; size=&quot;50&quot; null=&quot;true&quot; /&gt;
        &lt;column name=&quot;budget&quot; type=&quot;decimal&quot; /&gt;
    &lt;/table&gt;
    &lt;table name=&quot;order_tbl&quot; sequence=&quot;order_seq&quot; class=&quot;Order&quot; xml-name=&quot;order&quot;&gt;
        &lt;column name=&quot;id&quot; type=&quot;longint&quot;&gt;
            &lt;primary-key /&gt;
        &lt;/column&gt;
        &lt;column name=&quot;client_id&quot; type=&quot;longint&quot; null=&quot;false&quot;&gt;
            &lt;foreign-key table=&quot;client_tbl&quot; key=&quot;id&quot;/&gt;
        &lt;/column&gt;
        &lt;column name=&quot;dt&quot; type=&quot;datetime&quot; null=&quot;false&quot; default=&quot;sysdate&quot; /&gt;
        &lt;column name=&quot;memo&quot; type=&quot;string&quot; size=&quot;100&quot; /&gt;
        &lt;column name=&quot;total_sum&quot; type=&quot;decimal&quot; null=&quot;false&quot; /&gt;
        &lt;column name=&quot;paid_sum&quot; type=&quot;decimal&quot; default=&quot;0&quot; /&gt;
        &lt;column name=&quot;paid_dt&quot; type=&quot;datetime&quot; /&gt;
    &lt;/table&gt;
    &lt;relation type=&quot;one-to-many&quot;&gt;
        &lt;one class=&quot;Client&quot; property=&quot;orders&quot; /&gt;
        &lt;many class=&quot;Order&quot; property=&quot;owner&quot; /&gt;
    &lt;/relation&gt;
&lt;/schema&gt;</PRE><P>
If necessary, generate the SQL code to create tables and other
objects in your database schema. Specify the SQL dialect when using
the code generation tool, the following dialects are accepted:
<TT CLASS="western">SQLITE</TT>, <TT CLASS="western">MYSQL</TT>,
<TT CLASS="western">POSTGRES</TT>, <TT CLASS="western">ORACLE</TT>,
<TT CLASS="western">INTERBASE</TT>. Then you can apply the generated
script using standard means of your corresponding database engine,
e.g. for Oracle there is <TT CLASS="western">sqlplus</TT>.</P>
<PRE CLASS="western" STYLE="margin-top: 0.04in; margin-bottom: 0.2in">$(YBORM_ROOT)/bin/yborm_gen --ddl tutorial/tut3.xml MYSQL script.sql</PRE><P>
Generate the domain classes using the same tool with different
command line option. If you have already done this, but since then
the tables have received changes to their columns, you can use the
tool to update your domain classes' definitions to reflect the
changes made to the schema. 
</P>
<PRE CLASS="western" STYLE="margin-top: 0.04in; margin-bottom: 0.2in">$(YBORM_ROOT)/bin/yborm_gen --domain tutorial/tut3.xml domain/</PRE><P>
To operate on a database we will need <TT CLASS="western">Engine</TT>
object, that will manage all the operations at the SQL level. Object
<TT CLASS="western">Engine</TT> needs one of the following to operate
properly: 
</P>
<OL>
	<LI><P>a standalone database connection object <TT CLASS="western">SqlConnection</TT>,
	it will be used to execute all the SQL statements; 
	</P>
	<LI><P>or a pool of database connections <TT CLASS="western">SqlPool</TT>,
	that can manage the connections on its own, it can give a connection
	out of pool or create a new one per request. 
	</P>
</OL>
<P>In the example below the <TT CLASS="western">Engine</TT> object is
given a standalone connection. There is <TT CLASS="western">Session</TT>
object that works on top of Engine, it keeps track of state for each
object, it performs loading and saving fro/to the database as
necessary. 
</P>
<PRE CLASS="western">    auto_ptr&lt;SqlConnection&gt; conn(
        new SqlConnection(&quot;mysql+odbc://test1_usr:test1_pwd@test1_dsn&quot;));
    Engine engine(Engine::READ_WRITE, conn);
    Session session(init_schema(), &amp;engine);</PRE><P>
For sake of brevity the same can be done in one step:</P>
<PRE CLASS="western" STYLE="margin-top: 0.04in; margin-bottom: 0.2in">    Session session(init_schema(), &quot;mysql+odbc://test1_usr:test1_pwd@test1_dsn&quot;);</PRE><P>
And now to some querying examples. <STRONG>The simplest case: fetch
an object by its primary key.</STRONG> To do this just construct your
domain class object with a parameter-primary key value. The domain
object will automatically load all of its attributes on first attempt
to access any of them. Until this moment the object is in so called
&quot;Ghost&quot; state. This way of loading is called &quot;lazy
loading&quot;. Of course, this may be the case that there is no
object in the database with such primary key value as we specified,
then an exception will be thrown. 
</P>
<PRE CLASS="western">    Client client(session, 32738);
    try {
      cout &lt;&lt; client.name.value() &lt;&lt; endl;
    } catch (NoDataFound &amp;) {
      cerr &lt;&lt; &quot;No such client\n&quot;;
    }
SQL:
SELECT client_tbl.id, client_tbl.dt, client_tbl.name, client_tbl.email, client_tbl.budget FROM client_tbl WHERE client_tbl.id = ?</PRE><P>
Often we need to select an object by an arbitrary condition, which
may be not necessarily filtering by primary key. This is <STRONG>how
one can query for just one Client object with filter by name.</STRONG>
</P>
<PRE CLASS="western">    Client client = query&lt;Client&gt;(session)
      .filter_by(Client::c.name == name).one();
SQL:
SELECT client_tbl.id, client_tbl.dt, client_tbl.name, client_tbl.email, client_tbl.budget FROM client_tbl WHERE client_tbl.name = ?</PRE><P>
The template function <TT CLASS="western">query&lt;R&gt;()</TT>
returns a query object <TT CLASS="western">QueryObj</TT>, that we may
add filtering and sorting to. We may substitute a domain class for
parameter R. To select just one object use method <TT CLASS="western">one()</TT>
of <TT CLASS="western">QueryObj</TT> class. In case there's no such
object or there are more than one object then a <TT CLASS="western">NoDataFound</TT>
exception will be thrown. To select all of the objects by some
criteria use method <TT CLASS="western">all()</TT> of <TT CLASS="western">QueryObj</TT>
class, returning an iterable collection. The iteration over the
collection is also &quot;lazy&quot;, a new object is fetched on
iterator de-referencing. <STRONG>Example: find all unpaid orders
having total_sum more than given one.</STRONG> 
</P>
<PRE CLASS="western">    DomainResultSet&lt;Order&gt; rs = query&lt;Order&gt;(session)
      .filter_by(Order::c.total_sum &gt; Decimal(100)
           &amp;&amp; Order::c.paid_dt == YB_NULL).all();
    BOOST_FOREACH(Order order, rs) {
      cout &lt;&lt; order.id.value() &lt;&lt; &quot;,&quot;;
    }
SQL:
SELECT order_tbl.id, order_tbl.client_id, order_tbl.dt, order_tbl.memo, 
 order_tbl.total_sum, order_tbl.paid_sum, order_tbl.paid_dt 
FROM order_tbl WHERE (order_tbl.total_sum &gt; ?) AND (order_tbl.paid_dt IS NULL)</PRE><P>
Also there is method <TT CLASS="western">count()</TT> in class
<TT CLASS="western">QueryObj</TT>. This call transforms the query
into a sub-query then all the rows are counted in the sub-query. This
method returns an integer number. <STRONG>Example: count the number
of all orders for given client.</STRONG> 
</P>
<PRE CLASS="western">    cout &lt;&lt; &quot;Order count: &quot; &lt;&lt; query&lt;Order&gt;(session)
      .filter_by(Order::c.client_id == 32738).count() &lt;&lt; endl;
SQL:
SELECT COUNT(*) CNT FROM (
SELECT order_tbl.id, order_tbl.client_id, order_tbl.dt, order_tbl.memo, order_tbl.total_sum,
 order_tbl.paid_sum, order_tbl.paid_dt 
FROM order_tbl WHERE order_tbl.client_id = ?) X</PRE><P>
Sometimes it is necessary to implement a pager over a result set, to
achieve this we need only a slice of the original result set.
<TT CLASS="western">QueryObj</TT> has got method <TT CLASS="western">range()</TT>
for this purpose. It will modify the query in a way compatible with
selected SQL dialect. The first argument sets the starting item
position (numbered from 0), the second – ending item position (not
included in the range). <STRONG>Example: a pager over client's
orders, show items from 30 to 39 inclusive.</STRONG> 
</P>
<PRE CLASS="western">    DomainResultSet&lt;Order&gt; rs = query&lt;Order&gt;(session)
      .filter_by(Order::c.client_id == 32738)
      .order_by(Order::c.dt)
      .range(30, 40).all();
    BOOST_FOREACH(Order order, rs) {
      cout &lt;&lt; order.id &lt;&lt; &quot;,&quot;;
    }
SQL:
SELECT order_tbl.id, order_tbl.client_id, order_tbl.dt, order_tbl.memo, 
 order_tbl.total_sum, order_tbl.paid_sum, order_tbl.paid_dt 
FROM order_tbl WHERE (order_tbl.client_id = ?)
ORDER BY order_tbl.dt
LIMIT ? OFFSET ?
params: (32738, 10, 30)
</PRE><P>
Sometimes we need more complicated queries involving table joined
using foreign keys. In YB.ORM library we may pass as a parameter to
the template function <TT CLASS="western">query&lt;R&gt;()</TT> not
only a domain class but also a tuple (<TT CLASS="western">boost::tuple</TT>)
of several domain classes, their order is important. In the latter
case there must exist a declared relation between each two neighbour
items in the list, this is necessary to build proper join condition
for two corresponding tables. <STRONG>Let's assume, we need to
extract the orders filtered by total_sum, together with their
respective clients. The result must be sorted by client ID, then by
order ID.</STRONG> 
</P>
<PRE CLASS="western">    typedef tuple&lt;Order, Client&gt; Pair; 
    DomainResultSet&lt;Pair&gt; rs = query&lt;Pair&gt;(session) 
      .filter_by(Order::c.total_sum &gt; Decimal(&quot;3.14&quot;) 
           &amp;&amp; Client::c.budget != YB_NULL)
      .order_by(ExpressionList(Client::c.id, Order::c.id)).all(); 
    BOOST_FOREACH(Pair pair, rs) { 
      cout &lt;&lt; pair.get&lt;1&gt;().name.value() 
        &lt;&lt; &quot; &quot; &lt;&lt; pair.get&lt;0&gt;().total_sum.value() &lt;&lt; endl; 
    } 
SQL:
SELECT order_tbl.id, order_tbl.client_id, order_tbl.dt, order_tbl.memo, order_tbl.total_sum,
 order_tbl.paid_sum, order_tbl.paid_dt, 
 client_tbl.id, client_tbl.dt, client_tbl.name, client_tbl.email, client_tbl.budget 
FROM order_tbl JOIN client_tbl ON (client_tbl.ID = order_tbl.client_id) 
WHERE (order_tbl.total_sum &gt; ?) AND (client_tbl.budget IS NOT NULL) 
ORDER BY client_tbl.id, order_tbl.id </PRE><P>
In this example the lazy collection <TT CLASS="western">rs</TT> will
yield object pairs of Client, Order. 
</P>
</BODY>
</HTML>